﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;

namespace Swind
{
    public class CDFTableReader
    {
        public enum TableHeaderSection
        {
            typeRow = 0,
            headerRow = 1,
            headerDescriptionRow = 2,
            headerDetailRow = 3,
        }
        private const int tableContentIndex = 4;

        IWorkbook workbook;

        public bool Initialize(string sourceFilePath)
        {
            try
            {
                string fileExtension = Path.GetExtension(sourceFilePath);

                FileStream file = new FileStream(sourceFilePath, FileMode.Open, FileAccess.Read);

                if (fileExtension == ".xls")
                {
                    workbook = new HSSFWorkbook(file);
                }
                else if (fileExtension == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    Console.WriteLine(string.Concat("do not support file : ", sourceFilePath));
                    return false;
                }
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
                return false;
            }

            return true;
        }

        public List<string> GetTableHeaderRowList(int sheetIndex, TableHeaderSection tableHeaderSection)
        {
            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            if (sheet.LastRowNum == 0)
            {
                return null;
            }

            List<string> tableRowList = new List<string>();
            int tableSectionIndex = (int)tableHeaderSection;
            IRow headerRow = sheet.GetRow(tableSectionIndex);

            for (int i = 0; i < headerRow.LastCellNum; ++i)
            {
                ICell cell = headerRow.GetCell(i);
                if (cell != null)
                {
                    string cellData = cell.ToString().Trim();
                    if (!string.IsNullOrEmpty(cellData))
                    {
                        tableRowList.Add(cellData);
                    }
                }
            }

            return tableRowList;
        }

        public List<List<string>> GetTableContentList(int sheetIndex)
        {
            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            if (sheet.LastRowNum == 0)
            {
                return null;
            }

            List<List<string>> contentList = new List<List<string>>();
            for (int i = tableContentIndex; i < sheet.LastRowNum + 1; ++i)
            {
                IRow contentRow = sheet.GetRow(i);
                List<string> contentRowList = new List<string>();

                for (int j = 0; j < contentRow.LastCellNum; ++j)
                {
                    ICell cell = contentRow.GetCell(j);
                    if (cell != null)
                    {
                        string convertedCellValue = ConvertCellValue(cell);
                        contentRowList.Add(convertedCellValue);
                    }
                    else
                    {
                        contentRowList.Add(string.Empty);
                    }
                }

                if (!CheckEmpty(contentRowList))
                {
                    contentList.Add(contentRowList);
                }
            }

            return contentList;
        }

        public int GetSheetCount()
        {
            return workbook.NumberOfSheets;
        }

        public string GetSheetName(int sheetIndex)
        {
            string sheetName = workbook.GetSheetName(sheetIndex);
            return sheetName;
        }

        private bool CheckEmpty(List<string> stringList)
        {
            if (stringList != null)
            {
                int itemCount = stringList.Count;
                for (int i = 0; i < itemCount; ++i)
                {
                    if (!string.IsNullOrEmpty(stringList[i].Trim()))
                    {
                        return false;
                    }
                }
            }

            return true;
        }

        private string ConvertCellValue(ICell cell)
        {
            string stringCellValue = string.Empty;
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Blank:
                        stringCellValue = string.Empty;
                        break;
                    case CellType.Boolean:
                        stringCellValue = cell.BooleanCellValue.ToString();
                        break;
                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            stringCellValue = cell.DateCellValue.ToString();
                        }
                        else
                        {
                            stringCellValue = cell.NumericCellValue.ToString();
                        }
                        break;
                    case CellType.String:
                        stringCellValue = cell.StringCellValue.Trim();
                        break;
                    case CellType.Error:
                        stringCellValue = cell.ErrorCellValue.ToString();
                        break;
                    case CellType.Formula:
                        try
                        {
                            HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                            evaluator.EvaluateInCell(cell);
                            stringCellValue = cell.ToString();
                        }
                        catch
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                stringCellValue = cell.DateCellValue.ToString();
                            }
                            else
                            {
                                stringCellValue = cell.NumericCellValue.ToString();
                            }
                        }
                        break;
                    default:
                        stringCellValue = cell.ToString();
                        break;
                }
            }

            return stringCellValue;
        }
    }
}
